﻿using MainWebService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MainWebService
{
    public class DatabaseOperations
    {

        internal string CreateNewDatabase(string DBName, bool IsCaseSensitiveValidation, int CreatedBy, List<Scans> ScanDataList, List<int> ServiceDetails, ref int newDBId)
        {
            object messgae = null;
            object dbIDOutput = null;
            try
            {

                int dbId = 0;
                DAO d = new DAO();

                List<SqlParameter> param = new List<SqlParameter>();
                param.Add(d.AddParameter("@DBName", DBName));
                param.Add(d.AddParameter("@IsCaseSensitive", IsCaseSensitiveValidation));
                param.Add(d.AddParameter("@CreatedBy", CreatedBy));
                if (ServiceDetails != null && ServiceDetails.Count > 0)
                {
                    string serviceListcsv = string.Join(",", ServiceDetails.Select(x => x.ToString()).ToArray());
                    param.Add(d.AddParameter("@ServiceList", serviceListcsv));
                }
                else
                {
                    param.Add(d.AddParameter("@ServiceList", ""));
                }
                param.Add(d.AddParameter("@DBId", dbIDOutput, SqlDbType.Int, 10, ParameterDirection.InputOutput));
                param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
                d.ExecuteQuery("P_AddDataBase", param, ref dbIDOutput, ref messgae);
                if (dbIDOutput != null && Int32.TryParse(dbIDOutput.ToString(), out dbId))
                {
                    newDBId = dbId;
                    if (ScanDataList != null)
                    {
                        foreach (Scans item in ScanDataList)
                        {
                           ScanDetails.AddScanData(CreatedBy, dbId, item);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                messgae = ex.Message.ToString();
            }
            return messgae.ToString();
        }

        

        internal string RenameDatabase(int createdBy, int dbId, string newName)
        {
            object msg = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", dbId));
            param.Add(d.AddParameter("@NewName", newName));
            param.Add(d.AddParameter("@CreatedBy", createdBy));
            param.Add(d.AddParameter("@Messages", msg, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_RenameDB", param, ref msg);
            return msg.ToString();
        }

        internal string DeleteDatabase(int CreatedBy, int DbId)
        {
            object msg = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            param.Add(d.AddParameter("@Messages", msg, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_DeleteDB", param, ref msg);
            return msg.ToString();
        }

        internal DatabaseDetails GetDatabaseDetails(int CreatedBy, int DbId)
        {
            return GetDBDetail(DbId, CreatedBy);
        }

        private DatabaseDetails GetDBDetail(int DbId, int CreatedBy)
        {
            DatabaseDetails dbDetails = new DatabaseDetails();
            DataSet ds = new DataSet();
            DAO d = new DAO();
            DataTable dt = null;
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            ds = d.ExecuteDataSet("P_GetDBDetail", param);
            if (ds.Tables.Count > 0)
            {
                if (Utility.IsValidDataSet(ds, 0))
                {
                    dbDetails.DbId = DbId;
                    dbDetails.DBName = Convert.ToString(ds.Tables[0].Rows[0]["DBName"]);
                    dbDetails.IsCaseSensitiveValidation = Convert.ToBoolean(ds.Tables[0].Rows[0]["CaseSensitive"]);
                    dbDetails.LastUpdated = Convert.ToDateTime(ds.Tables[0].Rows[0]["UpdateDate"]);
                }
                if (Utility.IsValidDataSet(ds, 1))
                {
                    dbDetails.RowCount = Convert.ToInt32(ds.Tables[1].Rows[0]["Count"]);
                }
                if (Utility.IsValidDataSet(ds, 2))
                {
                    //Dictionary<int, string> lstService = new Dictionary<int, string>();
                    //for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
                    //{
                    //    lstService.Add(Convert.ToInt32(ds.Tables[2].Rows[i]["ServiceId"]), Convert.ToString(ds.Tables[2].Rows[i]["ServiceName"]));
                    //}
                    dt = ds.Tables[2].Copy();
                    dbDetails.GetServiceDetails = new DataSet();
                    dt = dt != null ? dt : new DataTable();
                    dbDetails.GetServiceDetails.Tables.Add(dt);
                }
                if (Utility.IsValidDataSet(ds, 3))
                {
                    Scans _sData = null;
                   dbDetails.ScanDataList = new List<Scans>();
                    for (int i = 0; i < ds.Tables[3].Rows.Count; i++)
                    {
                        _sData = new Scans();
                        _sData.ScanId = Utility.GetInt(ds.Tables[3].Rows[i][9]);
                        _sData.StudentIdNumber = Utility.GetInt(ds.Tables[3].Rows[i][0]);
                        _sData.FirstName = Utility.GetString(ds.Tables[3].Rows[i][1]);
                        _sData.LastName = Utility.GetString(ds.Tables[3].Rows[i][2]);
                        _sData.Grade = Utility.GetInt(ds.Tables[3].Rows[i][3]);
                        _sData.ValidityCode = Utility.GetInt(ds.Tables[3].Rows[i][4]);
                        _sData.Note1 = Utility.GetString(ds.Tables[3].Rows[i][5]);
                        _sData.Note2 = Utility.GetString(ds.Tables[3].Rows[i][6]);
                        _sData.Note3 = Utility.GetString(ds.Tables[3].Rows[i][7]);
                        _sData.Note4 = Utility.GetString(ds.Tables[3].Rows[i][8]);
                       dbDetails.ScanDataList.Add(_sData);
                    }
                }
            }
            else
            {
                dbDetails.UserMessage = "Unbale to get db details";
            }
            return dbDetails;
        }

        internal string AssociateServicToDatabase(int DbId, List<int> ServiceDetails, int CreatedBy)
        {
            object messgae = null;
            DAO d = new DAO();
            string serviceListcsv = string.Join(",", ServiceDetails.Select(x => x.ToString()).ToArray());
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            param.Add(d.AddParameter("@ServiceList", serviceListcsv));
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_AssociateService", param, ref messgae);
            return messgae.ToString();
        }

        internal List<int> GetDBIDList(int clientId)
        {
            List<int> lstDBIds = new List<int>();
            DataSet ds = new DataSet();
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@ClientId", clientId));
            ds = d.ExecuteDataSet("P_GetDBList", param);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    lstDBIds.Add(Convert.ToInt32(ds.Tables[0].Rows[i]["DataBaseId"]));
                }

            }
            return lstDBIds;
        }

        internal DataTable GetDBIDList(int DbId, int CreatedBy)
        {
            DataSet ds = new DataSet();
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            ds = d.ExecuteDataSet("P_GetTableData", param);
            return ds.Tables[0];
        }

        internal string DeleteScanData(int DbId, int CreatedBy, string barCodeValue, string HashCode)
        {
            object messgae = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            param.Add(d.AddParameter("@StudentIdNumber", barCodeValue));
            param.Add(d.AddParameter("@HashCode", HashCode));
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_DeleteScanData", param, ref messgae);
            return messgae.ToString();
        }

        internal string AddScanData(int dbId, int loggedInUserId, Scans scanData)
        {
            return ScanDetails.AddScanData(loggedInUserId, dbId, scanData);
        }

        internal string EmptyDatabase(int clientId, int dbId, string hashCode)
        {
            object messgae = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", dbId));
            param.Add(d.AddParameter("@CreatedBy", clientId));
            param.Add(d.AddParameter("@HashCode", hashCode));
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_EmptyDB", param, ref messgae);
            return messgae.ToString();
        }

        internal string UpdateScans(ScanData data, int DbId, int CreatedBy)
        {
            object messgae = null;
            DAO d = new DAO();
            List<SqlParameter> param = new List<SqlParameter>();
            param.Add(d.AddParameter("@DBId", DbId));
            param.Add(d.AddParameter("@CreatedBy", CreatedBy));
            param.Add(d.AddParameter("@StudentNo", data.StudentIdNumber));
            param.Add(d.AddParameter("@OldStudentNo", data.OldStudentIdNumber));
            param.Add(d.AddParameter("@FName", data.FirstName));
            param.Add(d.AddParameter("@LName", data.LastName));
            param.Add(d.AddParameter("@Grade", data.Grade));
            param.Add(d.AddParameter("@Validity", data.Validity));
            param.Add(d.AddParameter("@Note1", data.Note1));
            param.Add(d.AddParameter("@Note2", data.Note2));
            param.Add(d.AddParameter("@Note3", data.Note3));
            param.Add(d.AddParameter("@Note4", data.Note4));
            param.Add(d.AddParameter("@Messages", messgae, SqlDbType.VarChar, 500, ParameterDirection.InputOutput));
            d.ExecuteQuery("P_UpdateScans", param, ref messgae);
            return messgae.ToString();
        }
    }
}